﻿using Dapper;
using Domain.Entitys;
using Domain.Repositorys;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repositorys
{
    public class HFRepository : IHFRepository
    {
        public HFRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }
        string str = "Data Source=.;Initial Catalog=HR_DB;Integrated Security=True;Connect Timeout=30;Encrypt=False;";
        private readonly MyDbContext myDbContext;

        /// <summary>
        /// 添加人力资源档案信息
        /// </summary>
        /// <param name="hF"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> HFAddAsync(HF hF)
        {
            myDbContext.HF.Add(hF);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 根据1,2,3，级编号查询对应信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public List<HF> HFByKindId(string id)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = $"select * from hf where (FirstKindId = '{id}' or SecondKindId = '{id}' or ThirdKindId = '{id}') and Status = 'false' ";
                return sc.Query<HF>(sql).ToList();
            }
        }

        /// <summary>
        /// 分页查询人力资源档案信息
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Task<List<HF>> HFFenYeAsync(int pageSize, int pageIndex)
        {
            return myDbContext.HF.Where(h => h.CheckStatus == "未复核" && h.HumanFileStatus == "正常").Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
        }

        /// <summary>
        /// 查询人力资源总数
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> HFCountAsync()
        {
            return await myDbContext.HF.Where(h => h.CheckStatus == "未复核" && h.HumanFileStatus == "正常").CountAsync();
        }

        /// <summary>
        /// 根据id查询对应的人力资源信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<HF> HFByIDAsync(int id)
        {
            return await myDbContext.HF.FirstOrDefaultAsync(h => h.HufId == id);
        }

        /// <summary>
        /// 人力资源档案复核
        /// </summary>
        /// <param name="hF"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> HFReviewAsync(HF hF)
        {
            myDbContext.HF.Update(hF);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 修改人力资源档案实发，应发金额
        /// </summary>
        /// <param name="hF"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public Task<int> HFUpdateSalarySumAsync(List<HF> hF)
        {
            foreach (var item in hF)
            {
                myDbContext.HF.Update(item);
            }
            return myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 多条件查询人力资源档案数据
        /// </summary>
        /// <param name="fid"></param>
        /// <param name="sid"></param>
        /// <param name="tid"></param>
        /// <param name="mkid"></param>
        /// <param name="mid"></param>
        /// <param name="keywords"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public FenYe<HF> HFWhereList(FenYe<HF> fenYe, string fid, string sid, string tid, string mkid, string mid, string keywords, string startDate, string endDate)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                string where = " CheckStatus = '已复核'";
                bool hasCondition = false;

                if (!string.IsNullOrEmpty(fid) && !string.IsNullOrEmpty(tid) && !string.IsNullOrEmpty(sid))
                {
                    where += $" and (FirstKindId = '{fid}' and SecondKindId = '{sid}' and ThirdKindId = '{tid}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(mkid) && !string.IsNullOrEmpty(mid))
                {
                    where += $" and (HumanMajorKindId = '{mkid}' and HumanMajorId = '{mid}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                {
                    where += $@" and ( [RegistTime] > '{startDate}' and [RegistTime] < '{endDate}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(keywords))
                {
                    where += $" and (FirstKindName like '%{keywords}%' or SecondKindName like '%{keywords}%' or ThirdKindName like '%{keywords}%' or HumanMajorKindName like '%{keywords}%' or HumanMajorName like '%{keywords}%' or HumanName like '%{keywords}%') ";
                    hasCondition = true;
                }

                // 如果没有任何条件，添加一个恒为假的条件
                if (!hasCondition)
                {
                    if (string.IsNullOrEmpty(keywords))
                    {
                        where = "CheckStatus = '已复核'"; // 查询已复核全部数据
                    }
                    else
                    {
                        where = "1=0 "; // 什么都查不到
                    }
                }

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", fenYe.PageSize);
                parameters.Add("@keyName", "HufId");
                parameters.Add("@tabelName", "HF");
                parameters.Add("@where", where);
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);

                List<HF> list = sc.Query<HF>(sql, parameters).ToList();
                fenYe.list = list;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }

        /// <summary>
        /// 根据人力资源编号查询数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<HF> HFByIDReviewAsync(string hid)
        {
            return await myDbContext.HF.FirstOrDefaultAsync(h => h.HumanId == hid);
        }

        /// <summary>
        /// 多条件查询人力资源等待复核数据
        /// </summary>
        /// <param name="fenYe"></param>
        /// <param name="fid"></param>
        /// <param name="sid"></param>
        /// <param name="tid"></param>
        /// <param name="mkid"></param>
        /// <param name="mid"></param>
        /// <param name="keywords"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public FenYe<HF> HFWhereList2(FenYe<HF> fenYe, string fid, string sid, string tid, string mkid, string mid, string keywords, string startDate, string endDate)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                string where = " CheckStatus = '未复核'";
                bool hasCondition = false;

                if (!string.IsNullOrEmpty(fid) && !string.IsNullOrEmpty(tid) && !string.IsNullOrEmpty(sid))
                {
                    where += $" and (FirstKindId = '{fid}' and SecondKindId = '{sid}' and ThirdKindId = '{tid}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(mkid) && !string.IsNullOrEmpty(mid))
                {
                    where += $" and (HumanMajorKindId = '{mkid}' and HumanMajorId = '{mid}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                {
                    where += $@" and ( [RegistTime] > '{startDate}' and [RegistTime] < '{endDate}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(keywords))
                {
                    where += $" and (FirstKindName like '%{keywords}%' or SecondKindName like '%{keywords}%' or ThirdKindName like '%{keywords}%' or HumanMajorKindName like '%{keywords}%' or HumanMajorName like '%{keywords}%' or HumanName like '%{keywords}%') ";
                    hasCondition = true;
                }

                // 如果没有任何条件，添加一个恒为假的条件
                if (!hasCondition)
                {
                    if (string.IsNullOrEmpty(keywords))
                    {
                        where = "CheckStatus = '未复核'"; // 查询已复核全部数据
                    }
                    else
                    {
                        where = "1=0 "; // 什么都查不到
                    }
                }

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", fenYe.PageSize);
                parameters.Add("@keyName", "HufId");
                parameters.Add("@tabelName", "HF");
                parameters.Add("@where", where);
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);

                List<HF> list = sc.Query<HF>(sql, parameters).ToList();
                fenYe.list = list;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }

        /// <summary>
        /// 人力资源档案变更
        /// </summary>
        /// <param name="hF"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> HFChangeUpdateAsync(HF hF)
        {
            myDbContext.HF.Update(hF);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 多条件查询人力资源档案已复核，和正常状态数据
        /// </summary>
        /// <param name="fenYe"></param>
        /// <param name="fid"></param>
        /// <param name="sid"></param>
        /// <param name="tid"></param>
        /// <param name="mkid"></param>
        /// <param name="mid"></param>
        /// <param name="keywords"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public FenYe<HF> HFWhereList3(FenYe<HF> fenYe, string fid, string sid, string tid, string mkid, string mid, string keywords, string startDate, string endDate)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                string where = " CheckStatus = '已复核' and HumanFileStatus = '正常' and HumanProDesignation !='系统管理员'";
                bool hasCondition = false;

                if (!string.IsNullOrEmpty(fid) && !string.IsNullOrEmpty(tid) && !string.IsNullOrEmpty(sid))
                {
                    where += $" and (FirstKindId = '{fid}' and SecondKindId = '{sid}' and ThirdKindId = '{tid}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(mkid) && !string.IsNullOrEmpty(mid))
                {
                    where += $" and (HumanMajorKindId = '{mkid}' and HumanMajorId = '{mid}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                {
                    where += $@" and ( [RegistTime] > '{startDate}' and [RegistTime] < '{endDate}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(keywords))
                {
                    where += $" and (FirstKindName like '%{keywords}%' or SecondKindName like '%{keywords}%' or ThirdKindName like '%{keywords}%' or HumanMajorKindName like '%{keywords}%' or HumanMajorName like '%{keywords}%' or HumanName like '%{keywords}%') ";
                    hasCondition = true;
                }

                // 如果没有任何条件，添加一个恒为假的条件
                if (!hasCondition)
                {
                    if (string.IsNullOrEmpty(keywords))
                    {
                        where = "CheckStatus = '已复核' and HumanFileStatus = '正常' and HumanProDesignation !='系统管理员'"; // 查询已复核全部数据
                    }
                    else
                    {
                        where = "1=0 "; // 什么都查不到
                    }
                }

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", fenYe.PageSize);
                parameters.Add("@keyName", "HufId");
                parameters.Add("@tabelName", "HF");
                parameters.Add("@where", where);
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);

                List<HF> list = sc.Query<HF>(sql, parameters).ToList();
                fenYe.list = list;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }

        /// <summary>
        /// 人力资源档案删除
        /// </summary>
        /// <param name="hF"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> HFDeleteAsync(HF hF)
        {
            myDbContext.HF.Update(hF);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 查询人力资源档案已删除数据
        /// </summary>
        /// <param name="fenYe"></param>
        /// <param name="fid"></param>
        /// <param name="sid"></param>
        /// <param name="tid"></param>
        /// <param name="mkid"></param>
        /// <param name="mid"></param>
        /// <param name="keywords"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public FenYe<HF> HFWhereList4(FenYe<HF> fenYe, string fid, string sid, string tid, string mkid, string mid, string keywords, string startDate, string endDate)
        {
            using (SqlConnection sc = new SqlConnection(str))
            {
                string sql = "exec [dbo].[proc_Fenye] @pageSize, @keyName, @tabelName, @where , @currentPage, @rows out";
                string where = " CheckStatus = '已复核' and HumanFileStatus = '已删除' and HumanProDesignation !='系统管理员'";
                bool hasCondition = false;

                if (!string.IsNullOrEmpty(fid) && !string.IsNullOrEmpty(tid) && !string.IsNullOrEmpty(sid))
                {
                    where += $" and (FirstKindId = '{fid}' and SecondKindId = '{sid}' and ThirdKindId = '{tid}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(mkid) && !string.IsNullOrEmpty(mid))
                {
                    where += $" and (HumanMajorKindId = '{mkid}' and HumanMajorId = '{mid}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
                {
                    where += $@" and ( [RegistTime] > '{startDate}' and [RegistTime] < '{endDate}') ";
                    hasCondition = true;
                }
                if (!string.IsNullOrEmpty(keywords))
                {
                    where += $" and (FirstKindName like '%{keywords}%' or SecondKindName like '%{keywords}%' or ThirdKindName like '%{keywords}%' or HumanMajorKindName like '%{keywords}%' or HumanMajorName like '%{keywords}%' or HumanName like '%{keywords}%') ";
                    hasCondition = true;
                }

                // 如果没有任何条件，添加一个恒为假的条件
                if (!hasCondition)
                {
                    if (string.IsNullOrEmpty(keywords))
                    {
                        where = "CheckStatus = '已复核' and HumanFileStatus = '已删除' and HumanProDesignation !='系统管理员'"; // 查询已复核全部数据
                    }
                    else
                    {
                        where = "1=0 "; // 什么都查不到
                    }
                }

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@pageSize", fenYe.PageSize);
                parameters.Add("@keyName", "HufId");
                parameters.Add("@tabelName", "HF");
                parameters.Add("@where", where);
                parameters.Add("@currentPage", fenYe.CurrentPage);
                parameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);

                List<HF> list = sc.Query<HF>(sql, parameters).ToList();
                fenYe.list = list;
                fenYe.Rows = parameters.Get<int>("rows");

                return fenYe;
            }
        }

        /// <summary>
        /// 人力资源档案恢复
        /// </summary>
        /// <param name="hF"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> HFHFRestoreAsync(HF hF)
        {
            myDbContext.HF.Update(hF);
            return await myDbContext.SaveChangesAsync();
        }

        /// <summary>
        /// 查询人力资源档案已删除数据
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<HF>> HFDeleteInfoAsync()
        {
            return await myDbContext.HF.Where(h => h.HumanFileStatus == "已删除").ToListAsync();
        }

        /// <summary>
        /// 人力资源档案已删除数量
        /// </summary>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> HFDeleteInfoCount()
        {
            return await myDbContext.HF.Where(h => h.HumanFileStatus == "已删除").CountAsync();
        }

        /// <summary>
        /// 永久删除人力资源档案信息
        /// </summary>
        /// <param name="hF"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<int> HFDelete(HF hF)
        {
            myDbContext.HF.Remove(hF);
            return await myDbContext.SaveChangesAsync();
        }
    }
}
